//
// Created by HP on 2025/11/16.
//
#include "../include/Headler.h"

void handlerQueryOneWay(const Request& req, Response& res) {
    // 获取请求参数
    std::string userid = req.query_param("userid");
    std::string dst = req.query_param("destination");
    std::string src = req.query_param("source");
    std::string start = req.query_param("startdate");
    std::string pageStr = req.query_param("page");
    std::string sizeStr = req.query_param("size");

    // 校验分页参数（避免空值或非数字）
    int page, size;
    try {
        page = std::stoi(pageStr);
        size = std::stoi(sizeStr);
    } catch (...) {
        std::cerr<<"Page or Size not Valid"<<std::endl;
        page = 1;
        size = 10;
    }

    int offset = (page - 1) * size; // 计算偏移量（从0开始）

    // 构建SQL查询（核心：用GROUP BY合并同车次+同时间的数据）
    std::string queryStr =
            "SELECT "
            "name, "
            "startTime, "
            "endTime, "
            // 合并类型、价格、数量（用逗号分隔，后续解析）
            "GROUP_CONCAT(type) AS types, "
            "GROUP_CONCAT(price) AS prices, "
            "GROUP_CONCAT(cnt) AS cnts "
            "FROM tickets "
            "WHERE `from` like '%" + src + "%' AND `to` like '%" + dst + "%'";

    std::vector<std::string> trantypes = strTovec(req.query_param("traintype"));

    // 拼接类型筛选条件

    queryStr += "AND type IN (";

    for (auto& type: trantypes) {
        std::cout<<type<<std::endl;
         if (VaildTicket(type)){
           queryStr += "'" + Model::escapeSqlLiteral(type) + "',";
         } else {
            res.error(400,"Wrong type");
            return;
        }
    }

    queryStr.pop_back();

    queryStr += ")";

    // 拼接时间范围条件
    if (!start.empty()) {
        std::string endTime = addDay(start, 1); // 假设addDay能正确计算次日
        queryStr += " AND startTime >= '" + Model::escapeSqlLiteral(start) + "' AND startTime <= '" + Model::escapeSqlLiteral(endTime) + "'";
    }

    // 分组、排序、分页（核心：基于合并后的结果分页）
    queryStr += " GROUP BY name, startTime, endTime "  // 按车次+时间分组
                " ORDER BY startTime ASC "              // 按出发时间排序
                " LIMIT " + std::to_string(size) +      // 每页条数
                " OFFSET " + std::to_string(offset) + ";"; // 偏移量（分页）

//    std::cout << "SQL: " << queryStr << std::endl;

    // 执行查询
    std::vector<std::map<std::string, std::string>> result = DBConnector::getInstance()->query(queryStr);
    if (result.empty()) {
        res.json(R"({"Status":"ok","message":"No tickets found","data":[]})");
        return;
    }

    // 解析结果（将GROUP_CONCAT的字符串拆分为数组）
    std::vector<std::map<std::string ,std::string>> tickets;
    for (auto& row : result) {
        // 一行数据对应一个车次+时间的所有票类型
        std::map<std::string, std::string> ticket;
        ticket["name"] = row["name"];
        ticket["startTime"] = row["startTime"];
        ticket["endTime"] = row["endTime"];
        ticket["types"] = row["types"];
        ticket["prices"] = row["prices"];
        ticket["cnts"] = row["cnts"];
        std::vector<std::string> splits=split(ticket["types"], ',');
        ticket["type"] = ticketTypeToStr(splits[0]);
        tickets.push_back(ticket);
    }

    // 构造响应 - 使用新的JsonValue类型支持嵌套JSON
    std::map<std::string, JsonValue> resMap;

    // 将tickets数组转换为JsonValue数组
    resMap["data"] = JsonValue(tickets);
    resMap["page"] = JsonValue(std::stoi(pageStr));
    resMap["size"] = JsonValue(std::stoi(sizeStr));

    std::cout << "Response: " << toJson(resMap) << std::endl;
    res.success(resMap);
}

void handlerQueryRoundTrip(const Request& req, Response& res) {
    // 1. 解析请求参数（默认空字符串，避免野指针）
    std::string userid = req.query_param("userid");
    std::string dst = req.query_param("destination");
    std::string src = req.query_param("source");
    std::string traintypeStr = req.query_param("traintype");
    std::vector<std::string> trantypes = traintypeStr.empty() ? std::vector<std::string>() : strTovec(traintypeStr);
    std::string start = req.query_param("startdate");
    std::string end = req.query_param("enddate");
    std::string pageStr = req.query_param("page");
    std::string sizeStr = req.query_param("size");

    // 2. 必传参数校验（明确错误信息，符合RESTful规范）
    if (src.empty() || dst.empty()) {
        res.error(400, "Invalid parameter: source and destination are required");
        return;
    }
    if (start.empty()) {
        res.error(400, "Invalid parameter: startdate (departure date) is required");
        return;
    }
    if (end.empty()) {
        res.error(400, "Invalid parameter: enddate (return date) is required");
        return;
    }

    // 3. 分页参数解析（增强异常处理，避免stoi崩溃）
    int page = 1, size = 10;
    try {
        page = std::stoi(pageStr);
        size = std::stoi(sizeStr);
        // 边界限制：页码≥1，每页条数1-50（防止恶意分页攻击）
        page = std::max(page, 1);
        size = std::clamp(size, 1, 50); // C++17+，需兼容低版本可改用三目运算符
    } catch (const std::invalid_argument& e) {
        std::cerr << "Pagination parameter error (invalid format): " << e.what() << std::endl;
    } catch (const std::out_of_range& e) {
        std::cerr << "Pagination parameter error (out of range): " << e.what() << std::endl;
    } catch (...) {
        std::cerr << "Unknown pagination parameter error, use default (page=1, size=10)" << std::endl;
    }
    int offset = (page - 1) * size;

    // 4. 车票类型合法性校验（提前过滤无效类型，避免SQL冗余）
    std::vector<std::string> validTrantypes;
    for (const auto& type : trantypes) {
        validTrantypes.push_back(type);
    }

    // 5. 构建SQL查询（核心优化：防注入、兼容only_full_group_by、左连接不失效）
    std::ostringstream queryStr; // 用ostringstream拼接更高效、易维护
    queryStr << "SELECT "
                // 去程字段（保留原逻辑，GROUP_CONCAT确保顺序一致）
                "t.name AS name, "
                "t.startTime AS go_startTime, "
                "t.endTime AS go_endTime, "
                "GROUP_CONCAT(DISTINCT t.type ORDER BY t.type) AS go_types, "
                "GROUP_CONCAT(DISTINCT t.price ORDER BY t.type) AS go_prices, "
                "GROUP_CONCAT(DISTINCT t.cnt ORDER BY t.type) AS go_cnts, "
                // 返程状态（MAX聚合兼容only_full_group_by）
                "IFNULL(MAX(r.name), 0) AS has_return_ticket, "
                // 返程时间字段（聚合处理，符合分组规则）
                "MAX(r.startTime) AS return_startTime, "
                "MAX(r.endTime) AS return_endTime, "
                // 返程详情字段（GROUP_CONCAT保持原逻辑）
                "GROUP_CONCAT(DISTINCT r.type ORDER BY r.type) AS return_types, "
                "GROUP_CONCAT(DISTINCT r.price ORDER BY r.type) AS return_prices, "
                "GROUP_CONCAT(DISTINCT r.cnt ORDER BY r.type) AS return_cnts "
                "FROM tickets t "
                // 左连接匹配返程票（关键：将r的筛选条件移到ON子句，避免左连接失效）
                "LEFT JOIN tickets r ON "
                "   t.name = r.name "
                "   AND r.`from` like '%" << Model::escapeSqlLiteral(dst) << "%' "
                                                                         "   AND r.`to` like '%" << Model::escapeSqlLiteral(src) << "%' "
                                                                                                                                "   AND r.cnt > 0 "
             // 返程类型筛选（仅当用户指定类型时添加）
             << (validTrantypes.empty() ? "" : "   AND r.type IN (")
            ;
    // 拼接返程类型条件（仅有效类型）
    for (size_t i = 0; i < validTrantypes.size(); ++i) {
        if (i > 0) queryStr << ", ";
        queryStr << "'" << Model::escapeSqlLiteral(validTrantypes[i]) << "'";
    }
    if (!validTrantypes.empty()) {
        queryStr << ") ";
    }
    // 返程时间条件（移到ON子句，保证左连接特性）
    queryStr << "   AND r.startTime BETWEEN '" << Model::escapeSqlLiteral(start) << " 00:00:00' AND '" << Model::escapeSqlLiteral(end) << " 23:59:59' "
                                                                                                                                          // 去程条件（基础过滤，优化查询性能）
                                                                                                                                          "WHERE t.`from` like '%" << Model::escapeSqlLiteral(src) << "%' "
                                                                                                                                                                                                  "  AND t.`to` like '%" << Model::escapeSqlLiteral(dst) << "%' "
                                                                                                                                                                                                                                                        "  AND t.cnt > 0 ";
    // 去程类型筛选（仅当用户指定类型时添加）
    if (!validTrantypes.empty()) {
        queryStr << "  AND t.type IN (";
        for (size_t i = 0; i < validTrantypes.size(); ++i) {
            if (i > 0) queryStr << ", ";
            queryStr << "'" << Model::escapeSqlLiteral(validTrantypes[i]) << "'";
        }
        queryStr << ") ";
    }
    // 去程时间条件（精确到天，避免时分秒干扰）
    queryStr << "  AND t.startTime BETWEEN '" << Model::escapeSqlLiteral(start) << " 00:00:00' AND '" << Model::escapeSqlLiteral(start) << " 23:59:59' "
                                                                                                                                           // 分组、排序、分页（符合SQL标准）
                                                                                                                                           "GROUP BY t.name, t.startTime, t.endTime "
                                                                                                                                           "ORDER BY t.startTime ASC, t.name ASC "
                                                                                                                                           "LIMIT " << size << " OFFSET " << offset << ";";

    // 调试用：打印SQL（生产环境建议通过日志系统输出，避免敏感信息泄露）
    std::cout << "Round trip query SQL: " << queryStr.str() << std::endl;

    // 6. 执行SQL查询（增强错误处理，避免数据库连接异常崩溃）
    auto dbInstance = DBConnector::getInstance();
    if (!dbInstance) {
        std::cerr << "Database connector initialization failed" << std::endl;
        res.error(500, "Internal server error: database connection failed");
        return;
    }
    auto result = dbInstance->query(queryStr.str());

    // 7. 结果封装（处理NULL值，避免前端解析异常）
    std::map<std::string, JsonValue> resMp;
    std::vector<std::map<std::string, std::string>> tickets;

    for (auto& row : result) {
        std::map<std::string, std::string> ticket;
        // 去程字段（默认空字符串，避免NULL值）
        ticket["name"] = row["name"];
        ticket["go_startTime"] = row["go_startTime"];
        ticket["go_endTime"] = row["go_endTime"];
        ticket["go_types"] = row["go_types"];
        ticket["go_prices"] = row["go_prices"];
        ticket["go_cnts"] = row["go_cnts"];
        // 返程字段（无返程时返回空字符串，前端统一处理）
        ticket["has_return_ticket"] = row["has_return_ticket"];
        ticket["return_startTime"] = row["return_startTime"];
        ticket["return_endTime"] = row["return_endTime"];
        ticket["return_types"] = row["return_types"];
        ticket["return_prices"] = row["return_prices"];
        ticket["return_cnts"] = row["return_cnts"];

        std::vector<std::string> splits=split(ticket["go_types"], ',');
        ticket["type"] = ticketTypeToStr(splits[0]);
        tickets.push_back(ticket);
    }

    // 8. 响应封装（确保page/size为数字类型，前端无需转换）
    resMp["data"] = JsonValue(tickets);
    resMp["page"] = JsonValue(page);       // 用解析后的int，避免字符串转数字错误
    resMp["size"] = JsonValue(size);       // 同上
    resMp["total"] = JsonValue(static_cast<int>(tickets.size())); // 新增当前页条数，方便前端校验

    res.success(resMp);
}
/**
 * 单次换乘查询处理程序（仅支持一次换乘）
 * 逻辑：匹配「出发地→换乘站」的去程票 + 「换乘站→目的地」的换乘票，确保换乘时间合理
 * @param req HTTP请求对象，参数包含：source(出发地)、destination(目的地)、traintype(车次类型，单个值)、startdate(出发日期)、page/size(分页)
 * @param res HTTP响应对象，返回换乘组合结果
 */
void handlerQueryTransfer(const Request& req, Response& res) {
    // 1. 解析请求参数（适配换乘场景，traintype 为单个字符串，无返程日期）
    std::string userid = req.query_param("userid");
    std::string dst = req.query_param("destination");
    std::string src = req.query_param("source");
    std::vector<std::string> trantypes = strTovec(req.query_param("traintype")); // 恢复类型筛选
    std::string start = req.query_param("startdate");     // 出发日期（必传，换乘全程在同一天）
    std::string pageStr = req.query_param("page");
    std::string sizeStr = req.query_param("size");

    std::map<std::string, JsonValue> resMp;

    // 2. 必传参数校验（换乘场景核心参数：出发地、目的地、出发日期）
    if (src.empty() || dst.empty()) {
        res.error(400, "Source and destination cannot be empty");
        return;
    }
    if (start.empty()) {
        res.error(400, "Missing startdate parameter");
        return;
    }
    // 校验出发地和目的地不能相同（换乘无意义）
    if (src == dst) {
        res.error(400, "Source and destination cannot be the same");
        return;
    }

    // 3. 分页参数解析（和往返查询逻辑一致，限制范围）
    int page = 1, size = 10;
    try {
        if (!pageStr.empty()) page = std::stoi(pageStr);
        if (!sizeStr.empty()) size = std::stoi(sizeStr);
        page = (page < 1) ? 1 : page;                  // 页码最小为1
        size = (size < 1 || size > 50) ? 10 : size;    // 每页条数1-50条
    } catch (...) {
        std::cerr << "Page or Size not Valid, use default (page=1, size=10)" << std::endl;
    }
    int offset = (page - 1) * size;

    // 5. 构建SQL查询（核心：去程票 + 换乘票 关联，单次换乘）
    std::string queryStr =
            "SELECT "
            // 去程票信息（t1：出发地→换乘站）
            "t1.name AS go_train_name, "
            "t1.`from` AS go_from, "
            "t1.`to` AS transfer_station, "  // 换乘站（t1的终点 = t2的起点）
            "t1.startTime AS go_startTime, "
            "t1.endTime AS go_endTime, "
            "GROUP_CONCAT(DISTINCT t1.type ORDER BY t1.type) AS go_types, "
            "GROUP_CONCAT(DISTINCT t1.price ORDER BY t1.type) AS go_prices, "
            "GROUP_CONCAT(DISTINCT t1.cnt ORDER BY t1.type) AS go_cnts, "
            // 换乘票信息（t2：换乘站→目的地）
            "t2.name AS transfer_train_name, "
            "t2.startTime AS transfer_startTime, "
            "t2.endTime AS transfer_endTime, "
            "GROUP_CONCAT(DISTINCT t2.type ORDER BY t2.type) AS transfer_types, "
            "GROUP_CONCAT(DISTINCT t2.price ORDER BY t2.type) AS transfer_prices, "
            "GROUP_CONCAT(DISTINCT t2.cnt ORDER BY t2.type) AS transfer_cnts, "
            // 换乘总信息
            "TIMESTAMPDIFF(MINUTE, t1.endTime, t2.startTime) AS transfer_duration, " // 换乘时长（分钟）
            "(t1.price + t2.price) AS total_price " // 总票价（取同类型票价求和，此处为单类型求和示例）
            "FROM tickets t1 "
            // 自连接匹配换乘票（INNER JOIN 确保既有去程也有换乘程）
            "INNER JOIN tickets t2 ON "
            "   t1.`to` = t2.`from` "                          // 核心：t1的终点 = t2的起点（换乘站）
            "   AND TIMESTAMPDIFF(MINUTE, t1.endTime, t2.startTime) BETWEEN 15 AND 180 " // 换乘时长15-180分钟（合理范围）
            "   AND t2.cnt > 0 "                                // 换乘票有库存
            // 基础筛选条件
            "WHERE "
            "   t1.`from` like '%" + Model::escapeSqlLiteral(src) + "%' "  // 去程起点=用户出发地
                                                                "   AND t2.`to` like '%" + Model::escapeSqlLiteral(dst) + "%' "  // 换乘终点=用户目的地
                                                                                                                      "   AND t1.cnt > 0 "                                      // 去程票有库存
                                                                                                                      "   AND DATE(t1.startTime) = '" + Model::escapeSqlLiteral(start) + "' "  // 去程日期=用户指定日期
                                                                                                                                                                                         "   AND DATE(t2.startTime) = '" + Model::escapeSqlLiteral(start) + "' "  // 换乘日期=用户指定日期（同天换乘）
            ; // 车次类型筛选（可选）

    // 6. 拼接类型筛选条件（处理空数组，同步去程和返程）
    if (!trantypes.empty()) {
        queryStr += " AND t.type IN (";
        std::string returnTypeCond; // 存储返程票类型筛选条件
        for (auto& type : trantypes) {
            if (VaildTicket(type)) {
                std::string escapedType = Model::escapeSqlLiteral(type);
                queryStr += "'" + escapedType + "',";
                returnTypeCond += "'" + escapedType + "',";
            } else {
                res.error(400, "Invalid train type: " + type);
                return;
            }
        }
        // 移除去程类型的最后一个逗号
        queryStr.pop_back();
        queryStr += ") ";
        // 返程票类型同步筛选（避免返程类型与用户选择不一致）
        if (!returnTypeCond.empty()) {
            returnTypeCond.pop_back();
            queryStr += " AND r.type IN (" + returnTypeCond + ") ";
        }
    } else {
        // 若用户未传traintype，不筛选类型（匹配所有有效类型）
        std::cerr << "No train type specified, match all valid types" << std::endl;
    }

    // 7. 分组、排序、分页（确保结果唯一且有序）
    queryStr += " GROUP BY "
                "t1.name, t1.startTime, t1.endTime, "  // 唯一标识一趟去程
                "t2.name, t2.startTime, t2.endTime "    // 唯一标识一趟换乘程
                "ORDER BY "
                "t1.startTime ASC, "  // 先按去程出发时间排序
                "transfer_duration ASC, "  // 再按换乘时长升序（更便捷）
                "total_price ASC "  // 最后按总票价升序
                "LIMIT " + std::to_string(size) +
                " OFFSET " + std::to_string(offset) + ";";

    // 调试用：打印最终SQL（生产环境可注释）
    std::cout << "Transfer query SQL: " << queryStr << std::endl;

    // 8. 执行查询并处理结果
    auto result = DBConnector::getInstance()->query(queryStr);

    std::vector<std::map<std::string, std::string>> transferTickets;

    for (auto& row : result) {
        std::map<std::string, std::string> ticket;
        // 去程票信息
        ticket["go_train_name"] = row["go_train_name"];
        ticket["go_from"] = row["go_from"];
        ticket["transfer_station"] = row["transfer_station"]; // 换乘站
        ticket["go_startTime"] = row["go_startTime"];
        ticket["go_endTime"] = row["go_endTime"];
        ticket["go_types"] = row["go_types"];
        ticket["go_prices"] = row["go_prices"];
        ticket["go_cnts"] = row["go_cnts"];
        // 换乘票信息
        ticket["transfer_train_name"] = row["transfer_train_name"];
        ticket["transfer_startTime"] = row["transfer_startTime"];
        ticket["transfer_endTime"] = row["transfer_endTime"];
        ticket["transfer_types"] = row["transfer_types"];
        ticket["transfer_prices"] = row["transfer_prices"];
        ticket["transfer_cnts"] = row["transfer_cnts"];
        // 换乘总信息
        ticket["transfer_duration"] = row["transfer_duration"]; // 换乘时长（分钟）
        ticket["total_price"] = row["total_price"];             // 总票价
        ticket["destination"] = dst;                             // 最终目的地（冗余字段，方便前端）

        transferTickets.push_back(ticket);
    }

    // 8. 构建响应结果（和往返查询格式一致）
    resMp["data"] = JsonValue(transferTickets);
    resMp["page"] = JsonValue(page);
    resMp["size"] = JsonValue(size);
    resMp["total"] = JsonValue(static_cast<int>(transferTickets.size())); // 实际返回条数（如需总条数需单独查COUNT）

    res.success(resMp);
}
/**
 * 订单筛选查询处理程序（支持按时间、车次、站点筛选用户订单）
 * 逻辑：根据查询类型、关键词类型、时间范围筛选用户的订单，关联车票表获取完整信息
 * @param req HTTP请求对象，参数包含：
 *        checktype: 查询类型（必填，orderdate=按订单创建日期查、startdate=按车次出发日期查）
 *        keywordtype: 关键词类型（可选，bytrainid=按车次ID、bydestination=按目的地、bysource=按出发地、default=无额外筛选）
 *        keyword: 查询关键词（与keywordtype对应，非default时必填）
 *        starttime: 开始时间（必填，对应checktype的时间字段）
 *        endtime: 结束时间（必填，对应checktype的时间字段）
 *        userid: 用户ID（必填，筛选当前用户的订单）
 *        page/size: 分页参数（可选，默认第1页，每页10条）
 * @param res HTTP响应对象，返回筛选后的订单及关联车票详情
 */
void handlerQueryRefund(const Request& req, Response& res) {
    if (! Middleware::requiredAuth(req)) {
        res.error(401, "Permission denied due to insufficient privileges");
    }

    // 1. 解析请求参数
    std::string checktype = req.query_param("checktype");    // 核心查询类型（orderdate/startdate）
    std::string keywordtype = req.query_param("keywordtype");// 关键词类型（bytrainid/bydestination/bysource/default）
    std::string keyword = req.query_param("keyword");        // 查询关键词（非default时必填）
    std::string start = req.query_param("starttime");        // 开始时间（必填）
    std::string end = req.query_param("endtime");            // 结束时间（必填）
    std::string userid = req.query_param("userid");          // 用户ID（必填，筛选用户订单）
    std::string pageStr = req.query_param("page");
    std::string sizeStr = req.query_param("size");

    std::map<std::string, JsonValue> resMp;

    // 2. 必传参数校验（核心参数不能为空）
    if (checktype.empty()) {
        res.error(400, "Missing checktype parameter (valid values: orderdate/startdate)");
        return;
    }
    if (start.empty() || end.empty()) {
        res.error(400, "Missing starttime or endtime parameter");
        return;
    }
    if (userid.empty()) {
        res.error(400, "Missing userid parameter");
        return;
    }

    // 3. 校验查询类型合法性
    std::vector<std::string> validCheckTypes = {"orderdate", "startdate"};
    if (std::find(validCheckTypes.begin(), validCheckTypes.end(), checktype) == validCheckTypes.end()) {
        res.error(400, "Invalid checktype: " + checktype + " (valid values: orderdate/startdate)");
        return;
    }

    // 4. 校验关键词类型合法性 + 关键词必填校验
    std::vector<std::string> validKeywordTypes = {"default", "bytrainid", "bydestination", "bysource"};
    if (std::find(validKeywordTypes.begin(), validKeywordTypes.end(), keywordtype) == validKeywordTypes.end()) {
        res.error(400, "Invalid keywordtype: " + keywordtype + " (valid values: default/bytrainid/bydestination/bysource)");
        return;
    }
    // 非default关键词类型时，关键词必填
    if (keywordtype != "default" && keyword.empty()) {
        res.error(400, "Missing keyword parameter (required for keywordtype=" + keywordtype + ")");
        return;
    }

    // 5. 校验时间范围合法性（开始时间不能晚于结束时间）
    if (start > end) {
        res.error(400, "starttime cannot be later than endtime");
        return;
    }

    // 6. 分页参数解析（与前序函数逻辑一致，限制范围）
    int page = 1, size = 10;
    try {
        if (!pageStr.empty()) page = std::stoi(pageStr);
        if (!sizeStr.empty()) size = std::stoi(sizeStr);
        page = (page < 1) ? 1 : page;                  // 页码最小为1
        size = (size < 1 || size > 50) ? 10 : size;    // 每页条数1-50条
    } catch (...) {
        std::cerr << "Page or Size not Valid, use default (page=1, size=10)" << std::endl;
    }
    int offset = (page - 1) * size;

    // 7. 转义参数（防SQL注入，核心！）
    std::string escapedUserid = Model::escapeSqlLiteral(userid);
    std::string escapedStart = Model::escapeSqlLiteral(start);
    std::string escapedEnd = Model::escapeSqlLiteral(end);
    std::string escapedKeyword = Model::escapeSqlLiteral(keyword);

    // 8. 构建SQL查询（关联orders和tickets表，纯粹筛选逻辑）
    std::string queryStr;
    // 基础字段：订单表+车票表核心字段（精简实用，贴合需求）
    std::string baseFields =
            "o.order_id AS order_id, "            // 订单ID
            "o.userid AS user_id, "              // 用户ID
            "o.createdAt AS order_create_time, " // 订单创建时间
            "t.trainid AS train_id, "            // 车次ID
            "t.name AS train_name, "             // 车次名称
            "t.`from` AS from_station, "         // 出发站
            "t.`to` AS to_station, "             // 目的地
            "t.startTime AS train_start_time, "  // 车次出发时间
            "t.endTime AS train_end_time, "      // 车次到达时间
            "t.price AS ticket_price, ";          // 车票价格

    // 基础FROM和JOIN（左关联确保订单都能查询到，即使车票信息缺失）
    std::string baseFrom =
            "FROM orders o "
            "LEFT JOIN tickets t ON o.ticketid = t.id "
            "WHERE o.userid = '" + escapedUserid + "' ";  // 仅筛选当前用户的订单

    // 8.1 按查询类型拼接时间条件（核心筛选维度）
    if (checktype == "orderdate") {
        // 按订单创建日期筛选（精确到时间点）
        queryStr = "SELECT " + baseFields + baseFrom +
                   "AND o.createdAt BETWEEN '" + escapedStart + "' AND '" + escapedEnd + "' ";
    } else if (checktype == "startdate") {
        // 按车次出发日期筛选（精确到天，忽略时分秒）
        queryStr = "SELECT " + baseFields + baseFrom +
                   "AND DATE(t.startTime) BETWEEN '" + escapedStart + "' AND '" + escapedEnd + "' ";
    }

    // 8.2 按关键词类型拼接筛选条件（辅助筛选维度）
    if (keywordtype == "bytrainid") {
        // 按车次ID精确筛选
        queryStr += "AND t.trainid = '" + escapedKeyword + "' ";
    } else if (keywordtype == "bydestination") {
        // 按目的地精确筛选
        queryStr += "AND t.`to` = '" + escapedKeyword + "' ";
    } else if (keywordtype == "bysource") {
        // 按出发地精确筛选
        queryStr += "AND t.`from` = '" + escapedKeyword + "' ";
    }
    // keywordtype=default时，不添加额外筛选条件

    // 8.3 排序、分页（按订单创建时间降序，最新订单在前）
    queryStr +=
            "ORDER BY o.createdAt DESC "
            "LIMIT " + std::to_string(size) +
            " OFFSET " + std::to_string(offset) + ";";

    // 调试用：打印最终SQL（生产环境可注释）
    std::cout << "Order Filter Query SQL: " << queryStr << std::endl;

    // 9. 执行查询并处理结果
    auto result = DBConnector::getInstance()->query(queryStr);

    // 10. 计算总条数（通过LIMIT前的SQL查询，确保总条数准确）
    std::string countSql = queryStr.substr(0, queryStr.find("ORDER BY"));
    countSql = "SELECT COUNT(*) AS total " + countSql.substr(countSql.find("FROM"));
    auto countResult = DBConnector::getInstance()->query(countSql);
    int total = 0;
    if (!countResult.empty()) {
        total = std::stoi(countResult[0]["total"]);
    }

    // 11. 空结果处理
    if (result.empty()) {
        resMp["data"] = JsonValue(std::vector<std::map<std::string, std::string>>());
        resMp["page"] = JsonValue(page);
        resMp["size"] = JsonValue(size);
        resMp["total"] = JsonValue(total);
        resMp["total_pages"] = JsonValue((total + size - 1) / size);
        res.success(resMp);
        return;
    }

    // 12. 封装订单数据（字段与查询结果严格对应）
    std::vector<std::map<std::string, std::string>> orderList;
    for (auto& row : result) {
        std::map<std::string, std::string> order;
        // 订单核心信息
        order["order_id"] = row["order_id"];
        order["user_id"] = row["user_id"];
        order["order_create_time"] = row["order_create_time"];
        // 关联车票信息
        order["train_id"] = row["train_id"];
        order["train_name"] = row["train_name"];
        order["from_station"] = row["from_station"];
        order["to_station"] = row["to_station"];
        order["train_start_time"] = row["train_start_time"];
        order["train_end_time"] = row["train_end_time"];
        order["ticket_price"] = row["ticket_price"];
        orderList.push_back(order);
    }

    // 13. 构建响应结果（与前序函数格式统一，方便前端适配）
    resMp["data"] = JsonValue(orderList);
    resMp["page"] = JsonValue(page);
    resMp["size"] = JsonValue(size);
    resMp["total"] = JsonValue(total);
    resMp["total_pages"] = JsonValue((total + size - 1) / size); // 总页数，支持前端分页控件

    res.success(resMp);
}

void handlerQueryCheckPoint(const Request& req, Response& res) {
//    std::cout<<"Recerive QueryCheckPoint"<<std::endl;
    std::string station = req.query_param("station");
    std::string start = req.query_param("startdate");
    std::string trainid = req.query_param("trainid");

    if (trainid.empty()) {
        res.error(400, "Bad Request: Missing trainid parameter");
        return;
    }

    std::string querySql = "SELECT p.name FROM checks c LEFT JOIN stations s LEFT JOIN tickets t LEFT JOIN p WHERE s.id = c.station_id AND t.id = c.ticket_id AND p.id = c.point_id AND s.name = '"
            + Model::escapeSqlLiteral(station) + "' AND t.ticketid = '" + Model::escapeSqlLiteral(trainid) + "';";

    auto result = DBConnector::getInstance()->query(querySql);
//    std::cout<<"Arrival here"<<std::endl;
    std::map<std::string, JsonValue> resMp;

    if (result.empty()) {
        res.success(resMp);
        return;
    }

    resMp["data"] = JsonValue(result[0]["name"]);
    resMp["page"] = JsonValue(1);
    resMp["size"] = JsonValue(10);
    res.success(resMp);
}

void handlerQueryOnSale(const Request& req, Response& res) {
    std::string station = req.query_param("station");
    std::string start = req.query_param("startdate");
    std::string pageStr = req.query_param("page");
    std::string sizeStr = req.query_param("size");

    std::map<std::string , JsonValue> resMp;
    resMp["time"] = JsonValue("08:00:00");
    res.success(resMp);
}

